﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CFM.Common;
using System.Xml;
/// <summary>
/// Summary description for DBHelp
/// </summary>
namespace CFM.DAL
{
    public class DataHelper
    {
        #region Property
        //=====================Property===============================
        private SqlCommand cm;
        private SqlConnection cn;
        public string error = string.Empty;
        //===================End Property===============================
        #endregion
        
        //===============================Funtion Basic===========================================
        public DataHelper()
        {
            string strCn = GetConnectString(); // "Data Source=.;Initial Catalog=CFM;Integrated Security=True";
            cn = new SqlConnection(strCn);            
        }

        public void Connect()
        {
            try
            {
                if (cn.State == ConnectionState.Closed)
                    cn.Open();
            }
            catch (Exception ex)
            {
                error = ex.Message;
            }
        }
        public void DisConnect()
        {
            if (cn.State == ConnectionState.Open)
                cn.Close();
        }

        public bool ExcuteNonQuery(string sql,SqlParameter[] prs)
        {
            try
            {
                Connect();
                cm = new SqlCommand(sql, cn);
                cm.CommandType = CommandType.Text;
                if (prs != null)
                    cm.Parameters.AddRange(prs);
                cm.ExecuteNonQuery();
                DisConnect();

                return true;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return false;
            }            
        }

        public SqlDataReader ExcuteDataReader(string sql, SqlParameter[] prs)
        {
            try
            {
                Connect();
                cm = new SqlCommand(sql, cn);
                cm.CommandType = CommandType.Text;
                if(prs != null)
                    cm.Parameters.AddRange(prs);
                SqlDataReader dr = cm.ExecuteReader();
                return dr; ;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return null;
            }
        }
        public DataTable ExcuteDataTable(string sql, SqlParameter[] prs)
        {
            try
            {
                Connect();
                cm = new SqlCommand(sql, cn);
                cm.CommandType = CommandType.Text;
                if (prs != null)
                    cm.Parameters.AddRange(prs);
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cm);
                da.Fill(dt);
                return dt ;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return null;
            }
        }

        public List<T> MapDataReaderToObject<T>(SqlDataReader dr)
        {
            List<T> list = new List<T>();
            if(dr != null)
            while (dr.Read())
            {
                T b = (T)Activator.CreateInstance(typeof(T));
                foreach (var pr in typeof(T).GetProperties())
                {
                    if (pr.CanWrite)
                    {
                        try
                        {
                            var value = Convert.ChangeType(dr[pr.Name], pr.PropertyType);
                            if(pr.PropertyType.Name.Equals(typeof(string).Name))
                                value = (value as string).Trim();
                            pr.SetValue(b, value, null);
                        }
                        catch { }
                    }
                }
                list.Add(b);
            }
            DisConnect();
            return list;
        }

        private string GetConnectString()
        {
            string cn = "";
            XmlDocument doc = new XmlDocument();
            doc.Load(PathHelper.ConfigFolder + "\\Config.xml");
            XmlNodeList nodeList = doc.SelectNodes("/connect");
            if (nodeList.Count > 0)
            {
                cn += "Data Source=" + nodeList[0].Attributes["server"].Value
                     + ";Initial Catalog=" + nodeList[0].Attributes["database"].Value
                     + ";User ID=" + nodeList[0].Attributes["userid"].Value
                     + ";Password=" + nodeList[0].Attributes["userpass"].Value;                
            }
            return cn;
        }
    }
}